# coding = UTF-8

from GBaseConnector import connect, GBaseError
import ibm_db
import time
from py2neo import Graph, Node, Relationship
import datetime
import json
import numpy as np
import pandas as pd

configforGbase = {'host': '26.47.99.251', 'port': 5258, 'database': 'pms25', 'user': 'gbase',
                  'passwd': 'gbase20110531', 'connect_timeout': 388000, 'charset': 'utf8'}


#   10DKX-71441   taiping
#   10DKX-496481  minping
#   10DKX-100545  nanzhu
#   10.134.157.86:7687
# n4j:
# uri: bolt: // 10.134.157.215: 7687
# username: n4j
# password: jsepc01!
def getLine(BDZ_name):
    try:
        BDZ_name = BDZ_name
        graph = Graph('bolt://localhost:7687', username='neo4j', password='123456')

        sql = '''
                match p = (a)-[r:CORE_LINE]->(b) where #{tSbZwycDkxVo.dkxId}
                return a,r,b
                '''
        sql = sql.replace('#{tSbZwycDkxVo.dkxId}', BDZ_name)
        # df = graph.run(sql).to_data_frame()
        df = graph.run(sql)
        df = np.array(df.data())
        return df
    except Exception as e:
        print(e)


def getbdz(dkx_id):
    try:
        BDZ_name = dkx_id
        graph = Graph('bolt://localhost:7687', username='neo4j', password='123456')
        sql = '''
                match p = (a:NewSubstation)-[r:LINE]->(b) where #{tSbZwycDkxVo.dkxId}
                with distinct a
                return a.`IdentifiedObject.name` as 变电站, id(a) as id
        '''
        sql = sql.replace('#{tSbZwycDkxVo.dkxId}', BDZ_name)

        df = graph.run(sql).to_data_frame()
        return df
    except Exception as e:
        print(e)


def getBDZwz(bdzname):
    try:
        connGbase = connect()
        connGbase.connect(**configforGbase)
        curGbase = connGbase.cursor()
        getDkxTzSQL = '''
                SELECT DISTINCT 
                    tts.geo_positon
                FROM t_psr_tf_station tts
                WHERE tts.name = #{tSbZwycDkxVo.dkxId}'''
        getDkxTzSQL = getDkxTzSQL.replace('#{tSbZwycDkxVo.dkxId}', bdzname)
        curGbase.execute(getDkxTzSQL)
        result = curGbase.fetchall()
        return result
    except Exception as e:
        print(e)
    finally:
        connGbase.close()
        curGbase.close()


def main(yaosha):
    a = pd.read_csv('D:\pycharm Project\duokuixian\docs\lianluo.csv')
    bb = np.array(a['collect(distinct r1.dkx_id)'][13]).tolist()
    bb = bb[1:-2]
    c = bb.split(',')
    print(c)
    aa = ''
    for i in c:
        te = 'or r.dkx_id=' + "'" + i + "' "
        aa = aa + te
    bdz = getbdz(aa[3:])
    print(aa[3:])
    lianluo = getLine(aa[3:])
    print('我', bdz)

    bdzstr = bdz['变电站'].tolist()

    tempx = []
    tempy = []
    for i in bdzstr:
        BDZstr =  "'" + i + "'"
        # if i != bdzstr[-1]:
        #     BDZstr = BDZstr + ","
        # BDZstr = BDZstr+')'
        #print(BDZstr)

        # print('22',lianluo)
        bdz_wz = getBDZwz(BDZstr)

        print(bdz_wz)
        ii = bdz_wz[0]
        c = ii[0].split(',')
        c[0] = float(c[0])
        c[1] = float(c[1])
        tempx.append(c[0])
        tempy.append(c[1])
    bdz['zuobiaox'] = tempx
    bdz['zuobiaoy'] = tempy
    print('BDZ', bdz)
    # write = pd.ExcelWriter(
    #     'D:\\BDZ.xlsx')
    # bdz.to_excel(write)
    if yaosha == 1:
        jieguo = lianluo
    else:
        jieguo = bdz
    #print(lianluo)
    return jieguo


if __name__ == '__main__':
    b = main(2)
    # print(b[0]['b'].labels)
    # print(type(b))
    # print(b[0]['r']['dkx_id'])
    print(b.values[0][0])
    print(b['zuobiaox'])

